﻿using LambdaToSql.Extended;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace WebDemo
{
    public partial class Index : System.Web.UI.Page
    {
        LambdaToSql.SqlClient DB = new LambdaToSql.SqlClient();
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                //Query();
                //Inser();
                //Update();
                //Delete();
                //Tran(); 

                //Ado();
            }
        }

        #region 查询

        /// <summary>
        /// 查询
        /// </summary>
        public void Query()
        {
            //查询全部
            var list = DB.QueryTable<EntityModel.Table_ID>().ToList();

            //Find主键查找,支持Guid 和int 自增主键
            var entity = DB.QueryTable<EntityModel.Table_ID>().Find(200);

            //In查询
            var arr = new int?[] { 100, 101, 102, 103 }.ToList();
            var list1 = DB.QueryTable<EntityModel.Table_ID>(ex => arr.Contains(ex.ID)).ToList();

            //Not In 查询 
            var list2 = DB.QueryTable<EntityModel.Table_ID>(ex => ex.ID.ExNotIn(arr)).ToList();//有问题
            var list2_1 = DB.QueryTable<EntityModel.Table_ID>(ex => arr.NotContains(ex.ID)).ToList();//有问题

            // Like  查询
            var list3 = DB.QueryTable<EntityModel.Table_ID>().Where(ex => ex.LoginName.Contains("15")).ToList();//(LoginName like '%15%')
            var list4 = DB.QueryTable<EntityModel.Table_ID>().Where(ex => ex.LoginName.NotContains("15")).ToList();//(LoginName not like '%15%') //有问题
            var list5 = DB.QueryTable<EntityModel.Table_ID>().Where(ex => ex.LoginName.StartsWith("15")).ToList();//(LoginName like '15%')
            var list6 = DB.QueryTable<EntityModel.Table_ID>().Where(ex => ex.LoginName.EndsWith("15")).ToList();//(LoginName like '%15')

            //排序
            var list7 = DB.QueryTable<EntityModel.Table_ID>().OrderBy(ex => ex.CreateTime).OrderByDescending(ex => ex.LoginName).ToList();

            //分组
            var list8 = DB.QueryTable<EntityModel.Table_ID>().GroupBy(ex => new { ex.LoginName, ex.UserName }).ToList();

            //只取特定字段
            var list9 = DB.QueryTable<EntityModel.Table_ID>().Select(ex => new { ex.LoginName, ex.UserName }).ToList();

            //top N
            var list10 = DB.QueryTable<EntityModel.Table_ID>().Take(10).ToList();
            //第几页
            var list11 = DB.QueryTable<EntityModel.Table_ID>().Skip(2).Take(10).ToList();

            //取第一条数据
            var list12 = DB.QueryTable<EntityModel.Table_ID>().First();
            var list13 = DB.QueryTable<EntityModel.Table_ID>().FirstOrDefault();

            //分页  2005,2008使用row_number分页,2012以上使用offset分页形式
            int total = 0;
            var list14 = DB.QueryTable<EntityModel.Table_ID>().Skip(15).Take(30).ToPageList(ref total);

            //分组 select 比原始去重性能要高一些
            DB.QueryTable<EntityModel.Table_ID>().GroupBy(ex => new { ex.UserName, ex.LoginName })
                                                    .Select(ex => new { ex.UserName, ex.LoginName })
                                                    .ToList();

            //判断满足条件的数据是否存在
            var flag = DB.QueryTable<EntityModel.Table_ID>().Any();
            //判断满足条件的数据是否存在
            var flag1 = DB.QueryTable<EntityModel.Table_ID>(ex => ex.ID == 2000).Any();
        }
        #endregion

        #region 添加数据

        /// <summary>
        /// 添加数据
        /// </summary>
        public void Inser()
        {
            //添加单个实体对象
            var entity = new EntityModel.Table_ID()
            {
                LoginName = "登录用户:",
                UserName = "用户名:",
                PassWord = "密码-",
                Gender = "男",
                IsDelete = 0,
                Mobile = "15804066511",
                Remark = "备注",
                Address = "地址:",
                CreateTime = DateTime.Now
            };
            var ret = DB.InsertTble(entity).ExecuteNonQuery();//返回主键值

            //只添加某几列
            var i = DB.InsertTble(entity).InsertColumns(ex => new { ex.LoginName, ex.UserName, ex.Remark }).ExecuteNonQuery();

            //忽略某些列
            var i1 = DB.InsertTble(entity).IgnoreColumns(ex => new { ex.Mobile, ex.PassWord }).ExecuteNonQuery();
        }
        #endregion

        #region 更新数据

        /// <summary>
        /// 更新数据
        /// </summary>
        public void Update()
        {
            //更新单个实体对象
            var entity = DB.QueryTable<EntityModel.Table_ID>(ex => ex.ID == 200).FirstOrDefault();
            entity.PassWord = "12345";
            entity.LoginName = "LambdaToSql";
            entity.UserName = "LambdaToSql1";
            var i = DB.UpdateTble(entity).ExecuteNonQuery();

            //更新特定字段,不指定不更新
            var i1 = DB.UpdateTble(entity).UpdateColumns(ex => new { ex.PassWord }).ExecuteNonQuery();

            //忽略特定字段,其他字段都更新
            var i2 = DB.UpdateTble(entity).IgnoreColumns(ex => new { ex.UserName, ex.PassWord }).ExecuteNonQuery();

            //条件更新 不需要取出实体对象 直接数据库更新             
            var i3 = DB.UpdateTble(new EntityModel.Table_ID() { PassWord = "123456", LoginName = "12" }).Where(ex => ex.ID == 101).ExecuteNonQuery(true);
        }
        #endregion

        #region 删除

        /// <summary>
        /// 删除
        /// </summary>
        public void Delete()
        {
            //删除单个实体,通过主键删除 
            var entity = DB.QueryTable<EntityModel.Table_ID>(ex => ex.ID == 200).FirstOrDefault();
            var i = DB.DeleteTble<EntityModel.Table_ID>(entity).ExecuteNonQuery();

            //条件删除 支持查询里面的所有条件写法
            var i1 = DB.DeleteTble<EntityModel.Table_ID>(ex => ex.ID == 201).ExecuteNonQuery();
        }
        #endregion

        #region 事务

        /// <summary>
        /// 事务
        /// </summary>
        public void Tran()
        {
            var sqlClient = new LambdaToSql.SqlClient();
            try
            {
                sqlClient.BeginTran();//开启事务

                //添加单个实体对象
                var entity = new EntityModel.Table_ID()
                {
                    LoginName = "登录用户:",
                    UserName = "用户名:",
                    PassWord = "密码-",
                    IsDelete = 0,
                    CreateTime = DateTime.Now
                };
                var entity1 = new EntityModel.Table_ID()
                {
                    LoginName = "登录用户:",
                    UserName = "在破败中崛起，在寂灭中复苏。沧海成尘，雷电枯竭，那一缕幽雾又一次临近大地，世间的枷锁被打开了，一个全新的世界就此揭开神秘的一角:",
                    PassWord = "密码-",
                    IsDelete = 0,
                    CreateTime = DateTime.Now
                };
                var entity2 = new EntityModel.Table_ID()
                {
                    LoginName = "登录用户:",
                    UserName = "用户名:",
                    PassWord = "密码-",
                    IsDelete = 0,
                    CreateTime = DateTime.Now
                };


                sqlClient.InsertTble(entity).ExecuteNonQuery();
                sqlClient.InsertTble(entity1).ExecuteNonQuery();//错误 UserName太长=>回滚
                sqlClient.InsertTble(entity2).ExecuteNonQuery();

                sqlClient.CommitTran();//提交事务
            }
            catch (Exception ex)
            {
                sqlClient.RollbackTran();//回滚事务
            }
        }
        #endregion

        #region Ado

        public void Ado()
        {
            var sql = "select top(10) * from table_id";
            var sdr = DB.Ado.ExecuteReader(sql);
            var list = new List<string>();
            while (sdr.Read())
            {
                list.Add(sdr[0].ToString());
            }
            sdr.Close();

            var Dt = DB.Ado.ExecuteTable(sql);

            var ls1 = DB.Ado.ExecuteScalar(sql);
            var ls = DB.Ado.ExecuteScalars(sql);

            var i = DB.Ado.ExecuteNonQuery("update top (10) table_id set imgurl = 'img1'");
        }

        #endregion
    }
}